﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMS.DAL.DAHelpers;
using SMS.Entities;
using System.Data;
using System.Data.SqlTypes;
using SMS.Entities.Enums;

namespace SMS.DAL
{
    public class CustomerDA : BaseDA<Customer>
    {
        public override Customer SaveObject(string userName, Customer tObject)
        {
            #region Procedure
            /*
             * ALTER PROCEDURE [dbo].[USP_INS_UPDATE_CUSTOMER]
(@Customer_ID int,
@Customer_Name varchar(50),
@Customer_Address varchar(500),
@Customer_Mobile varchar(20),
@Customer_AlterMobile varchar(20),
@Customer_CreditLimit decimal(18,2),
@IsLive bit)
as
IF (@Customer_ID=0)
BEGIN
INSERT INTO [Shop].[dbo].[Customer]
           ([Customer_Name]
           ,[Customer_Address]
           ,[Customer_Mobile]
           ,[Customer_AlterMobile]
           ,[Customer_CreditLimit]
           ,[IsLive])
     VALUES
           (@Customer_Name
           ,@Customer_Address
           ,@Customer_Mobile
           ,@Customer_AlterMobile
           ,@Customer_CreditLimit
           ,@IsLive)
     SELECT @@IDENTITY
END
ELSE
BEGIN           
UPDATE [Shop].[dbo].[Customer]
   SET [Customer_Name] = @Customer_Name
      ,[Customer_Address] = @Customer_Address
      ,[Customer_Mobile] = @Customer_Mobile
      ,[Customer_AlterMobile] = @Customer_AlterMobile
      ,[Customer_CreditLimit] = @Customer_CreditLimit
      ,[IsLive] = @IsLive
 WHERE Customer_ID=@Customer_ID
 END
             * */
            #endregion
            try
            {
                dbHelper.AddParameter("@Customer_ID", tObject.Customer_ID);
                dbHelper.AddParameter("@Customer_Name", tObject.Customer_Name);
                dbHelper.AddParameter("@Customer_Address", tObject.Customer_Address);
                dbHelper.AddParameter("@Customer_Mobile", tObject.Customer_Mobile);
                dbHelper.AddParameter("@Customer_AlterMobile", tObject.Customer_AlterMobile);
                dbHelper.AddParameter("@Customer_CreditLimit", tObject.Customer_CreditLimit);
                dbHelper.AddParameter("@IsLive", tObject.IsLive);
                object objID = dbHelper.ExecuteScaler("USP_INS_UPDATE_CUSTOMER", System.Data.CommandType.StoredProcedure, System.Data.ConnectionState.Closed);
                if (Convert.ToInt32(objID) > 0)
                    tObject.Customer_ID = Convert.ToInt32(objID);
                LogActivity(userName, string.Format("Customer Saved with ID {0}", tObject.Customer_ID), tObject);
                return tObject;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, tObject);
                throw new Exception("Error Occured while Saving Customer");
            }
        }

        public override bool DeleteObject(string userName, Customer tObject)
        {
            throw new NotImplementedException();
        }

        public override List<Customer> GetObjectList(string userName, DateTime fromDate, DateTime toDate)
        {
            throw new NotImplementedException();
        }

        public override List<Customer> SearchObject(string userName, string SearchText)
        {
            #region Procedure
            /* ALTER PROCEDURE USP_SEARCH_CUSTOMER(@Search_Text varchar(25))
 as
 BEGIN
	SELECT [Customer_Name]
           ,[Customer_Address]
           ,[Customer_Mobile]
           ,[Customer_AlterMobile]
           ,[Customer_CreditLimit]
           ,[IsLive] FROM CUSTOMER
           WHERE [Customer_Name] LIKE '%'+ @Search_Text + '%' 
           OR [Customer_Mobile] LIKE '%'+ @Search_Text + '%' 
 END
             * */
            #endregion

            try
            {
                dbHelper.AddParameter("@Search_Text", string.IsNullOrWhiteSpace(SearchText) ? string.Empty : SearchText);
                var reader = dbHelper.ExecuteReader("USP_SEARCH_CUSTOMER", System.Data.CommandType.StoredProcedure, System.Data.ConnectionState.Closed);
                var list = new List<Customer>();
                while (reader.Read())
                {
                    list.Add(GetCustomerObject(reader));
                }
                LogActivity(userName, string.Format("Searched customer with text {0}", SearchText), null);
                return list;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw new Exception("Error while searching Customer");
            }
        }

        public decimal GetCustomerBalance(string userName, int customerID)
        {
            #region Procedure
            /**
             *  ALTER PROCEDURE USP_GET_CUSTOMER_BALANCE
 (@Customer_ID int)
 as
 BEGIN
 DECLARE @CREDIT DECIMAL(18,3), @DEBIT DECIMAL(18,3), @BALANCE DECIMAL(18,3)
 SELECT @CREDIT=SUM(Amount)  FROM CustomerTransaction
 WHERE Customer_ID=@Customer_ID AND CreditDebit='C'
 
 SELECT @DEBIT=SUM(Amount)  FROM CustomerTransaction
 WHERE Customer_ID=@Customer_ID AND CreditDebit='D'
 IF (@CREDIT IS NULL)
 SELECT @CREDIT=0
 IF (@DEBIT IS NULL)
 SELECT @DEBIT=0
 SELECT (@DEBIT-@CREDIT)
 END
             */
            #endregion
            try
            {
                dbHelper.AddParameter("@Customer_ID", customerID);
                dbHelper.AddParameter("@Start_Date", SqlDateTime.MinValue);
                dbHelper.AddParameter("@End_Date", SqlDateTime.MaxValue);
                var reader = dbHelper.ExecuteReader("USP_GET_CUSTOMER_BALANCE", CommandType.StoredProcedure, ConnectionState.Closed);
                decimal balance = 0;
                if (reader.Read() && !reader.IsDBNull(0))
                    balance = reader.GetDecimal(0);
                reader.Close();
                LogActivity(userName, string.Format("GetCustomerBalance of CustomerID {0}", customerID), null);
                return balance;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw new Exception("Error while getting Customer Balance");
            }
        }

        private Customer GetCustomerObject(IDataReader reader)
        {
            int i = -1;
            Customer obj = new Customer();
            if (!reader.IsDBNull(++i))
                obj.Customer_ID = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                obj.Customer_Name = reader.GetString(i);
            if (!reader.IsDBNull(++i))
                obj.Customer_Address = reader.GetString(i);
            if (!reader.IsDBNull(++i))
                obj.Customer_Mobile = reader.GetString(i);
            if (!reader.IsDBNull(++i))
                obj.Customer_AlterMobile = reader.GetString(i);
            if (!reader.IsDBNull(++i))
                obj.Customer_CreditLimit = reader.GetDecimal(i);
            if (!reader.IsDBNull(++i))
                obj.IsLive = reader.GetBoolean(i);
            return obj;
        }

        #region CustomerTransaction Related

        public int SaveCustomerTransation(string userName, CustomerTransaction transaction)
        {
            #region Procedure
            /*CREATE PROCEDURE USP_INSERT_UPDATE_CUSTOMER_TRANSACTION
(@Transaction_ID int,
@SalesMan_ID int,
@Customer_ID int,
@SaleHistory_ID int,
@CreditDebit char(1),
@TransactionDate DateTime,
@Amount decimal(18,3),
@IsLive bit)
as
BEGIN
IF (@Transaction_ID=0)
BEGIN
	INSERT INTO [Shop].[dbo].[CustomerTransaction]
           ([SalesMan_ID]
           ,[Customer_ID]
           ,[SaleHistory_ID]
           ,[CreditDebit]
           ,[TransactionDate]
           ,[Amount]
           ,[IsLive])
     VALUES
           (@SalesMan_ID
           ,@Customer_ID
           ,@SaleHistory_ID
           ,@CreditDebit
           ,@TransactionDate
           ,@Amount
           ,@IsLive)
     SELECT @@IDENTITY
END
ELSE
BEGIN
UPDATE [Shop].[dbo].[CustomerTransaction]
   SET [SalesMan_ID] = @SalesMan_ID
      ,[Customer_ID] = @Customer_ID
      ,[SaleHistory_ID] = @SaleHistory_ID
      ,[CreditDebit] = @CreditDebit
      ,[TransactionDate] = @TransactionDate
      ,[Amount] = @Amount
      ,[IsLive] = @IsLive
 WHERE Customer_Transaction_ID=@Transaction_ID
 SELECT -1
END
END
             */
            #endregion
            try
            {
                dbHelper.AddParameter("@Transaction_ID", transaction.Transaction_Id);
                dbHelper.AddParameter("@SalesMan_ID", transaction.SalesMan.SalesMan_ID);
                dbHelper.AddParameter("@Customer_ID", transaction.Customer.Customer_ID);
                dbHelper.AddParameter("@SaleHistory_ID", transaction.SaleHistory.Sale_ID);
                dbHelper.AddParameter("@CreditDebit", transaction.CreditDebit == TransactionType.Credit ? "C" : "D");
                dbHelper.AddParameter("@TransactionDate", transaction.TransactionDate);
                dbHelper.AddParameter("@Amount", transaction.Amount);
                dbHelper.AddParameter("@IsLive", transaction.IsLive);
                object id = dbHelper.ExecuteScaler("USP_INSERT_UPDATE_CUSTOMER_TRANSACTION", CommandType.StoredProcedure, ConnectionState.Closed);
                LogActivity(userName, string.Format("Saved Customer Transaction with ID {0}", transaction.Transaction_Id), null);
                if (id != null)
                    return Convert.ToInt32(id);
                return -1;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw new Exception("Error while doing the transaction");
            }
        }

        public List<CustomerTransaction> GetCustomerTransactionList(string userName, int customerID, DateTime fromDate, DateTime toDdate)
        {
            #region Procedure
            /*
             * ALTER PROCEDURE USP_SELECT_CUSTOMER_TRANSACTION
(@Customer_ID int,
@Start_Date date,
@End_Date date)
as
BEGIN
DECLARE @LASTBAL DECIMAL(18,3)
SELECT @LASTBAL=DBO.GET_CUSTOMER_BALANCE(@Customer_ID,'01-Jan-53',@Start_Date)
SELECT Customer_Transaction_ID,SalesMan_ID,SaleHistory_ID, CreditDebit,
TransactionDate,Amount FROM CustomerTransaction
WHERE Customer_ID=@Customer_ID and TransactionDate>@Start_Date and TransactionDate<=@End_Date
AND IsLive=1

SELECT @LASTBAL
END
             */
            #endregion
            try
            {
                dbHelper.AddParameter("@Customer_ID", customerID);
                dbHelper.AddParameter("@Start_Date", fromDate);
                dbHelper.AddParameter("@End_Date", toDdate);
                var reader = dbHelper.ExecuteReader("USP_SELECT_CUSTOMER_TRANSACTION", CommandType.StoredProcedure, ConnectionState.Closed);
                var result = new List<CustomerTransaction>();
                while (reader.Read())
                {
                    result.Add(GetCustomerTransactionFromReader(reader));
                }
                if (reader.NextResult() && reader.Read())
                {
                    var amount = reader.IsDBNull(0) ? 0 : reader.GetDecimal(0);
                    result.Insert(0, new CustomerTransaction { Amount = amount, CreditDebit = TransactionType.BalanceForwarded, TransactionDate = fromDate });
                }
                reader.Close();
                LogActivity(userName, string.Format("Viewed Customer Transaction Customer# {0}, From {1} - {2}", customerID, fromDate, toDdate), null);
                return result;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw new Exception("Error while getting the customer transaction list"); ;
            }
        }
        #endregion

        private CustomerTransaction GetCustomerTransactionFromReader(IDataReader reader)
        {
            var transaction = new CustomerTransaction();
            int i = -1;
            if (!reader.IsDBNull(++i))
                transaction.Transaction_Id = reader.GetInt32(i);
            if (!reader.IsDBNull(++i))
                transaction.SalesMan = new SalesManDetail { SalesMan_ID = reader.GetInt32(i) };
            if (!reader.IsDBNull(++i))
                transaction.SaleHistory = new SaleHistory { Sale_ID = reader.GetInt32(i) };
            if (!reader.IsDBNull(++i))
                transaction.CreditDebit = reader.GetString(i) == "C" ? TransactionType.Credit : TransactionType.Debit;
            if (!reader.IsDBNull(++i))
                transaction.TransactionDate = reader.GetDateTime(i);
            if (!reader.IsDBNull(++i))
                transaction.Amount = reader.GetDecimal(i);
            return transaction;

        }

        public List<ClsSaleDetail> GetSaleDetails(string userName, int saleID)
        {

            #region "Procedure"
            /*
             ALTER proc [dbo].[USP_SELECT_SALEDETAIL]     
(  
@SaleID int  
)   
AS      
BEGIN    
 SELECT 
     s.[Sales_ID]    
     ,pp.[Product_Name]
     ,s.[Quantity]    
     ,s.[SellingUnitPrice]    
     ,s.[BilledAmount]    
     ,s.[TotalAmount]    
     ,s.[Discount]         
          
      FROM SALEDETAIL s inner join productstockwise p on s.productstockwiseid=p.product_stock_id
	  inner join product pp on p.product_id=pp.product_id   
      WHERE s.Sales_ID=@SaleID and s.ISLIVE=1       
END
             */
            #endregion
            System.Data.IDataReader reader = null;
            try
            {
                dbHelper.AddParameter("@SaleID", saleID);
                reader = dbHelper.ExecuteReader("USP_SELECT_SALEDETAIL", System.Data.CommandType.StoredProcedure, System.Data.ConnectionState.Closed);
                List<ClsSaleDetail> history = new List<ClsSaleDetail>();
                while (reader.Read())
                    history.Add(GetSaleDetailObject(reader));
                reader.Close();

                return history;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw;
            }

        }

        private ClsSaleDetail GetSaleDetailObject(System.Data.IDataReader reader)
        {
            ClsSaleDetail detail = new ClsSaleDetail();
            int i = 0;
            detail.SalesID = reader.GetInt32(i++);
            detail.ProductName = reader.GetString(i++);
            detail.Quantity = reader.GetInt32(i++);
            detail.SellingUnitPrice = reader.GetDecimal(i++);
            detail.BilledAmount = reader.GetDecimal(i++);
            detail.TotalAmount = reader.GetDecimal(i++);
            detail.Discount = reader.GetDecimal(i++);

            return detail;
        }

    }
}
